package fisio.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Vector;

import fisio.connection.ConnectionManager;
import fisio.global.Util;
import fisio.model.Compromisso;
import fisio.model.Fisioterapeuta;
import fisio.model.Paciente;

public class CompromissoDAO {

	private static final String SQL_INSERT_COMPROMISSO = "INSERT INTO compromisso (id_f, id_p, data_horario, descricao) VALUES (?, ?, ?, ?)";
	private static final String SQL_GET_COMPROMISSO_BY_ID = "SELECT * FROM compromisso WHERE id_c = ?";
	private static final String SQL_GET_COMPROMISSOS = "SELECT * FROM compromisso ORDER BY data_horario";
	private static final String SQL_GET_COMPROMISSOS_HOJE = "SELECT * FROM compromisso WHERE id_f = ? AND DATE_FORMAT(data_horario,'%Y-%m-%d') = DATE_FORMAT(CURRENT_DATE(),'%Y-%m-%d') ORDER BY data_horario";
	private static final String SQL_GET_COMPROMISSOS_SEMANA = "SELECT * FROM compromisso WHERE id_f = ? AND DATE_FORMAT(data_horario,'%Y-%U') = DATE_FORMAT(CURRENT_DATE(),'%Y-%U') ORDER BY data_horario";
	private static final String SQL_GET_COMPROMISSOS_MES = "SELECT * FROM compromisso WHERE id_f = ? AND DATE_FORMAT(data_horario,'%Y-%m')= DATE_FORMAT(CURRENT_DATE(),'%Y-%m') ORDER BY data_horario";
	private static final String SQL_GET_COMPROMISSOS_PERIODO = "SELECT * FROM compromisso WHERE id_f = ? AND data_horario BETWEEN ? AND ? ORDER BY data_horario";
	private static final String SQL_DELETE_COMPROMISSO = "DELETE FROM compromisso WHERE id_c = ?";
	private static final String SQL_GET_COMPROMISSOS_BY_ID_F = "SELECT * FROM compromisso WHERE id_f = ? ORDER BY data_horario ASC";
	private static final String SQL_GET_COMPROMISSOS_BY_ID_P = "SELECT * FROM compromisso WHERE id_p = ? ORDER BY data_horario ASC LIMIT ?,15";
	private static final String SQL_UPDATE_COMPROMISSO = "UPDATE compromisso SET id_f = ?, id_p = ?, data_horario = ?, descricao = ? WHERE id_c = ?";
	private static final String SQL_CHECK_COMPROMISSO = "SELECT * FROM compromisso WHERE (id_p = ? AND data_horario = ?) OR (id_f = ? AND data_horario = ?) ";
	
	public static Compromisso getCompromisso(int id) {

		Connection conn = ConnectionManager.getConnection();
		Compromisso compromisso = null;

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_GET_COMPROMISSO_BY_ID);
			stmt.setInt(1, id);
			
			ResultSet rs = stmt.executeQuery();
			if(rs.next()) {
				compromisso = new Compromisso(
						rs.getInt(1),
						rs.getInt(2),
						rs.getInt(3),
						Util.convertTimestampSqlToDateUtil(rs.getTimestamp(4)),
						rs.getString(5)
						);
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return compromisso;
	}
	
	public static void insertCompromisso(Compromisso compromisso) {
		
		Connection conn = ConnectionManager.getConnection();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_INSERT_COMPROMISSO);
			stmt.setInt(1, compromisso.getIdF());
			if(compromisso.getIdP() > 0)
				stmt.setInt(2, compromisso.getIdP());
			else
				stmt.setNull(2,java.sql.Types.INTEGER);
			stmt.setTimestamp(3, Util.convertDateUtilToTimestampSql(compromisso.getData()));
			stmt.setString(4, compromisso.getDescricao());
			
			stmt.executeUpdate();
			
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na insercao: "+e.getMessage());
		}
	}
	
	public static boolean verificaDisponibilidade(Compromisso c){
		boolean retorno = false;
		Connection conn = ConnectionManager.getConnection();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_CHECK_COMPROMISSO);
			stmt.setInt(1, c.getIdP());
			stmt.setTimestamp(2, Util.convertDateUtilToTimestampSql(c.getData()));
			stmt.setInt(3, c.getIdF());
			stmt.setTimestamp(4, Util.convertDateUtilToTimestampSql(c.getData()));
			
			ResultSet rs = stmt.executeQuery();
			retorno = rs.next();
			
			stmt.close();
			conn.close();
			
			System.out.println(rs);
			

		} catch(SQLException e) {
			System.out.println("\nERRO na insercao: "+e.getMessage());
		} 
		
		return !retorno;
		
	}
	
	public static void updateCompromisso(int id, Compromisso compromisso) {
		
		Connection conn = ConnectionManager.getConnection();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_UPDATE_COMPROMISSO);
			stmt.setInt(1, compromisso.getIdF());
			stmt.setInt(2, compromisso.getIdP());
			stmt.setTimestamp(3, Util.convertDateUtilToTimestampSql(compromisso.getData()));
			stmt.setString(4, compromisso.getDescricao());
			stmt.setInt(5, id);
			
			stmt.executeUpdate();
			
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na edicao: "+e.getMessage());
		}
	}

	public static void deleteCompromisso(int id) {
		
		Connection conn = ConnectionManager.getConnection();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_DELETE_COMPROMISSO);
			stmt.setInt(1, id);
			
			stmt.executeUpdate();
			
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na remocao: "+e.getMessage());
		}
	}
	
	public static Vector<Compromisso> getCompromissosFisioterapeuta(Fisioterapeuta fisioterapeuta) {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Compromisso> todos = new Vector<Compromisso>();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_GET_COMPROMISSOS_BY_ID_F);
			stmt.setInt(1, fisioterapeuta.getId());
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				todos.add(new Compromisso(
						rs.getInt(1),
						rs.getInt(2),
						rs.getInt(3),
						Util.convertTimestampSqlToDateUtil(rs.getTimestamp(4)),
						rs.getString(5)
					)
				);
				
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return todos;
	}

	public static Vector<Compromisso> getCompromissosPaciente(int pagina, Paciente paciente) {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Compromisso> todos = new Vector<Compromisso>();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_GET_COMPROMISSOS_BY_ID_P);
			stmt.setInt(1, paciente.getId());
			stmt.setInt(2, pagina*15);
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				todos.add(
					new Compromisso(
						rs.getInt(1),
						rs.getInt(2),
						rs.getInt(3),
						Util.convertTimestampSqlToDateUtil(rs.getTimestamp(4)),
						rs.getString(5)
					)
				);
				
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return todos;
	}

	public static Vector<Compromisso> getCompromissos() {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Compromisso> todos = new Vector<Compromisso>();
	
		try {
	
			PreparedStatement stmt = conn.prepareStatement(SQL_GET_COMPROMISSOS);
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				todos.add(
					new Compromisso(
						rs.getInt(1),
						rs.getInt(2),
						rs.getInt(3),
						Util.convertTimestampSqlToDateUtil(rs.getTimestamp(4)),
						rs.getString(5)
					)
				);
				
			}
	
			rs.close();
			stmt.close();
			conn.close();
	
		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}
	
		return todos;
	}

	public static Vector<Compromisso> getCompromissosFisioterapeutaPeriodo(Fisioterapeuta f, Date dataInicio, Date dataFim) {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Compromisso> comps = new Vector<Compromisso>();
	
		try {
	
			PreparedStatement stmt = conn.prepareStatement(SQL_GET_COMPROMISSOS_PERIODO);
			stmt.setInt(1, f.getId());
			stmt.setTimestamp(2, Util.convertDateUtilToTimestampSql(dataInicio));
			stmt.setTimestamp(3, Util.convertDateUtilToTimestampSql(dataFim));
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				comps.add(new Compromisso(rs.getInt(1), rs.getInt(2), rs.getInt(3),	Util.convertTimestampSqlToDateUtil(rs.getTimestamp(4)),	rs.getString(5)));
			}
	
			rs.close();
			stmt.close();
			conn.close();
	
		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}
	
		return comps;
	}

	public static Vector<Compromisso> getCompromissosFisioterapeutaMes(Fisioterapeuta f) {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Compromisso> comps = new Vector<Compromisso>();
	
		try {
	
			PreparedStatement stmt = conn.prepareStatement(SQL_GET_COMPROMISSOS_MES);
			stmt.setInt(1, f.getId());
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				comps.add(new Compromisso(
						rs.getInt(1),
						rs.getInt(2),
						rs.getInt(3),
						Util.convertTimestampSqlToDateUtil(rs.getTimestamp(4)),
						rs.getString(5)
					)
				);
				
			}
	
			rs.close();
			stmt.close();
			conn.close();
	
		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}
	
		return comps;
	}

	public static Vector<Compromisso> getCompromissosFisioterapeutaSemana(Fisioterapeuta f) {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Compromisso> todos = new Vector<Compromisso>();
	
		try {
	
			PreparedStatement stmt = conn.prepareStatement(SQL_GET_COMPROMISSOS_SEMANA);
			stmt.setInt(1, f.getId());
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				todos.add(new Compromisso(
						rs.getInt(1),
						rs.getInt(2),
						rs.getInt(3),
						Util.convertTimestampSqlToDateUtil(rs.getTimestamp(4)),
						rs.getString(5)
					)
				);
				
			}
	
			rs.close();
			stmt.close();
			conn.close();
	
		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}
	
		return todos;
	}

	public static Vector<Compromisso> getCompromissosFisioterapeutaHoje(Fisioterapeuta f) {
		
		Connection conn = ConnectionManager.getConnection();
		Vector<Compromisso> comps = new Vector<Compromisso>();
	
		try {
	
			PreparedStatement stmt = conn.prepareStatement(SQL_GET_COMPROMISSOS_HOJE);
			stmt.setInt(1, f.getId());
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				comps.add(new Compromisso(rs.getInt(1), rs.getInt(2), rs.getInt(3), Util.convertTimestampSqlToDateUtil(rs.getTimestamp(4)), rs.getString(5)));
			}
	
			rs.close();
			stmt.close();
			conn.close();
	
		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}
	
		return comps;
	}
}
